package com.a51integrated.sfs2x;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;

import com.smartfoxserver.v2.db.IDBManager;
import com.smartfoxserver.v2.entities.User;
import com.smartfoxserver.v2.extensions.BaseServerEventHandler;
import com.smartfoxserver.v2.extensions.ExtensionLogLevel;
import com.smartfoxserver.v2.core.ISFSEvent;
import com.smartfoxserver.v2.core.SFSEventParam;

public class UserDisconnectHandler extends BaseServerEventHandler {

	@Override
	public void handleServerEvent(ISFSEvent event) {
		// TODO Auto-generated method stub

		User user = (User)event.getParameter(SFSEventParam.USER);
		String username = user.getName();		
		
		IDBManager dbManager = getParentExtension().getParentZone().getDBManager();
		Connection connection;
		try
		{
			connection = dbManager.getConnection();			
			
			// Remove the disconnected player from the game he was in in the games table, and decrement the numconnected value of that row by 1.
			PreparedStatement stmt = connection.prepareStatement("SELECT `names` FROM `games` WHERE player0='" + username + "' OR player1='" + username + "' OR player2='" + username + "' OR player3='" + username + "'");
			ResultSet res = stmt.executeQuery();
			
			if (!res.first())
			{
				// The player's name is not present anywhere in the games table. No need to remove him.
				trace("User disconnected, user '" + username + "' does not need to be deleted from any records");
			}
			else
			{
				boolean first = true;
				do
				{
					if (!first)
					{
						res.next();
					}
					
					String gameName = res.getString("names");
					
					trace("User disconnected, deleting user '" + username + "' from records for game '" + gameName + "'");
					
					// Now that gameName contains the name of a game that the player was part of, remove the player's name from this game and decrement the numconnected value.
					stmt = connection.prepareStatement("UPDATE `games` SET numconnected=(numconnected - 1) WHERE names='" + gameName + "'");
					stmt.execute();
					stmt = connection.prepareStatement("UPDATE `games` SET player0='' WHERE player0='" + username + "'");
					stmt.execute();
					stmt = connection.prepareStatement("UPDATE `games` SET player1='' WHERE player1='" + username + "'");
					stmt.execute();
					stmt = connection.prepareStatement("UPDATE `games` SET player2='' WHERE player2='" + username + "'");
					stmt.execute();
					stmt = connection.prepareStatement("UPDATE `games` SET player3='' WHERE player3='" + username + "'");
					stmt.execute();
					
					// Now remove the player from any table that contains game data.
					stmt = connection.prepareStatement("UPDATE `game_" + gameName + "_gamedata` SET owner='' WHERE owner='" + username + "'");
					stmt.execute();
					stmt = connection.prepareStatement("DELETE FROM `game_" + gameName + "_players` WHERE player='" + username + "'");
					stmt.execute();
				}
				while (!res.isLast());
			}
			
			// Now remove any redundant game tables.
			{
				// Firstly, count the number of rows in the first column of table games.
				stmt = connection.prepareStatement("SELECT COUNT('names') FROM games");
				res = stmt.executeQuery();
				
				// Verify that one record was found.
				if (!res.first())
				{
					connection.close();
					return;
				}
				
				// Store the number of rows. (Index starts at 1)
				int NumRows = res.getInt(1);
				
				// Now obtain the data itself.
				stmt = connection.prepareStatement("SELECT * FROM games");
				res = stmt.executeQuery();
				
				if (!res.first())
				{
					connection.close();
					return;
				}
				
				Collection<String> deprecated_names = new ArrayList<String>();	// Collection of game names that are now deprecated and should be removed from the database.
				
				// Get the information of the games. This loop is also used to construct a list of
				// games that are no longer in use (0 connected players).
				// These games must not be sent to the player and must be removed from the database (along with all tables associated with them).
				for (int i = 0; i < NumRows; ++i)
				{
					String name = res.getString("names");
					int connected = res.getInt("numconnected");
					
					if (connected <= 0)
					{
						deprecated_names.add(name);
					}
					
					// Advance the cursor.
					res.next();
				}
				
				// Now clean up any deprecated games and tables.
				if (deprecated_names.size() > 0)
				{
					String removeGamesString = "DELETE FROM games WHERE ";
					for (Iterator it = deprecated_names.iterator(); it.hasNext(); )
					{
						String iterator_value = (String)it.next();
						removeGamesString += "names='" + iterator_value + "' OR ";
						
						// Send two statements to delete the tables that were created specifically for a game.
						stmt = connection.prepareStatement("DROP TABLE IF EXISTS `game_" + iterator_value + "_players`");
						stmt.execute();
						stmt = connection.prepareStatement("DROP TABLE IF EXISTS `game_" + iterator_value + "_gamedata`");
						stmt.execute();
					}
					// Add a useless part to the end of the statement because there's a trailing || operator.
					removeGamesString += "0=1";
					
					// Now prepare and execute the statement to delete rows from the games table.
					stmt = connection.prepareStatement(removeGamesString);
					stmt.execute();
				}
			}
			
			connection.close();
		}
		catch(SQLException e)
		{
			trace(ExtensionLogLevel.WARN, "SQL Failed: " + e.toString());
		}
	}
}
